package com.xing.lei.happy.thrid.flh.sql;

import com.xing.lei.happy.thrid.flh.sql.service.MySqlSqlConverter;
import com.xing.lei.happy.thrid.flh.sql.service.OracleSqlConverter;
import com.xing.lei.happy.thrid.flh.sql.service.SqlServerSqlConverter;
import com.xing.lei.happy.thrid.flh.sql.service.SqlTypeConverter;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

/**
 * SQL转换器演示类
 * 用于展示不同数据库类型之间的SQL语句转换
 */
public class TxtSqlConverterDemo {
    /**
     * 主方法，演示SQL转换功能
     * 包含建表、新增列、修改列、插入数据等SQL语句的转换示例
     */
    public static void main(String[] args) {
        // 完整测试示例，包含所有类型
        // 1、原始sql为oracle类型；
        // 2、多个空格视为一个空格；
        // 3、同类sql写在一起用“；\n”分割;
        // 4、兼容大小写;
        List<String> sqlList = new ArrayList<>();
        // 示例：带主键和索引的建表语句
        // oracle || sqlserver || mysql
        //-------------------------------
        // VARCHAR2 || VARCHAR || VARCHAR
        // DATE || DATETIME2(7) || DATETIME
        // NUMBER || DECIMAL || DECIMAL
        // sqlList.add( "CREATE   TABLE CRB_ZCJL (\n" +
        //         "    ID VARCHAR2(50) NOT NULL,\n" +
        //         "    GRID VARCHAR2(20),\n" +
        //         "    CRBZCSJ DATE,\n" +
        //         "    CRBZCYF VARCHAR2(20),\n" +
        //         "    CRBZCJLWJ VARCHAR2(1000),\n" +
        //         "    BZ VARCHAR2(200),\n" +
        //         "    LRR VARCHAR2(20),\n" +
        //         "    LRRXM VARCHAR2(50),\n" +
        //         "    LRRQ DATE,\n" +
        //         "    LRJGBH VARCHAR2(50),\n" +
        //         "    LRJGMC VARCHAR2(100),\n" +
        //         "    XZQH VARCHAR2(50),\n" +
        //         "    XZQHMC VARCHAR2(100),\n" +
        //         "    XGR VARCHAR2(20),\n" +
        //         "    XGRXM VARCHAR2(50),\n" +
        //         "    XGRQ DATE,\n" +
        //         "    ZFBZ NUMBER(1)\n" +
        //         ")\n" +
        //         "PRIMARY  KEY   ID\n" + //非标sql,简化输入
        //         "INDEX  GRID,LRR;\n"); //不支持生成联合索引、唯一索引，全部处理为单个普通索引
        // // 新增列
        // sqlList.add("ALTER   TABLE CRB_ZCJL1 ADD BZ1 VARCHAR2(1000);\n" +
        //         "ALTER   TABLE CRB_ZCJL2 ADD BZ2 VARCHAR2(1000);");
        // // 修改列
        // sqlList.add("ALTER   TABLE CRB_ZCJL1 MODIFY BZ1 VARCHAR2(1000);\n" +
        //         "ALTER   TABLE CRB_ZCJL2 MODIFY BZ2 VARCHAR2(1000);");
        // // 新增行（默认主键为ID,未找到则找主键QXID,还未找到取第一个字段，详情见InsertStatement.getId()）
        // sqlList.add("insert  into XT_QXXX1 (ID, QXDM, QXMC, QXMS, QXJB, XGR, XGSJ, GNQX, MRXZ, SJQXID, CDLX, PID, PATH, CDBZ, BEANNAME, MENUNAME, SXH)\n" +
        //         "values ('170711', 'CRB_BGGL_ZCJL', '传染病自查记录', '传染病自查记录', 3, '1', sysdate, '', null, '170711', '3', '1707', 'crbZcjlEdit.search', '1', 'crbZcjl', '传染病自查记录', '3');\n" +
        //         "\n" +
        //         "insert   into XT_QXXX2 (ID, QXDM, QXMC, QXMS, QXJB, XGR, XGSJ, GNQX, MRXZ, SJQXID, CDLX, PID, PATH, CDBZ, BEANNAME, MENUNAME, SXH)\n" +
        //         "values ('170712', 'CRB_BGGL_ZCJL_INSERT', '传染病自查记录新增', '传染病自查记录新增', 3, '1', sysdate, '', null, '170712', '3', '1707', '', '4', '', '', null);\n" +
        //         "\n" +
        //         "insert into XT_QXXX (ID, QXDM, QXMC, QXMS, QXJB, XGR, XGSJ, GNQX, MRXZ, SJQXID, CDLX, PID, PATH, CDBZ, BEANNAME, MENUNAME, SXH)\n" +
        //         "values ('170713', 'CRB_BGGL_ZCJL_UPDATE', '传染病自查记录修改', '传染病自查记录修改', 3, '1', sysdate, '', null, '170713', '3', '1707', '', '4', '', '', null);\n" +
        //         "\n" +
        //         "insert into XT_QXXX (ID, QXDM, QXMC, QXMS, QXJB, XGR, XGSJ, GNQX, MRXZ, SJQXID, CDLX, PID, PATH, CDBZ, BEANNAME, MENUNAME, SXH)\n" +
        //         "values ('170714', 'CRB_BGGL_ZCJL_DELETE', '传染病自查记录删除', '传染病自查记录删除', 3, '1', sysdate, '', null, '170714', '3', '1707', '', '4', '', '', null);");
        sqlList = getSqlList();
        SqlTypeConverter oracleSqlConverter = new OracleSqlConverter();
        SqlTypeConverter sqlServerSqlConverter = new SqlServerSqlConverter();
        SqlTypeConverter mySqlSqlConverter = new MySqlSqlConverter();
        System.out.println(oracleSqlConverter.convert(sqlList));
        System.out.println(sqlServerSqlConverter.convert(sqlList));
        System.out.println(mySqlSqlConverter.convert(sqlList));
    }

    public static List<String> getSqlList() {
        List<String> sqlList = new ArrayList<>();

        // 使用类加载器读取 resources 下的 org_sql.txt
        try (InputStream is = SqlConverterDemo.class.getClassLoader().getResourceAsStream("sql/org_sql.txt");
             BufferedReader reader = new BufferedReader(new InputStreamReader(is))) {

            if (is == null) {
                System.err.println("资源文件 org_sql.txt 未找到，请确认是否已放入 resources 目录！");
                return null;
            }

            StringBuilder contentBuilder = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                contentBuilder.append(line).append("\n");
            }

            String content = contentBuilder.toString().trim();
            // 按照 ";\\s*\\n\\s*\\n" 分割 SQL 语句
            String[] sqls = content.split(";\\s*\\n\\s*\\n");

            // 添加非空 SQL 到 sqlList
            for (String sql : sqls) {
                if (sql.trim().length() > 0) {
                    sqlList.add(sql.trim());
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return sqlList;
    }
} 